CREATE USER MSPETSHOP4SERVICES  PROFILE DEFAULT IDENTIFIED BY "pass@word1" DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
					       
GRANT CREATE SESSION TO MSPETSHOP4SERVICES; 

CREATE SEQUENCE MSPETSHOP4SERVICES.USERID INCREMENT BY 1 
    START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE 
    CACHE 10000 ORDER;   

CREATE SEQUENCE MSPETSHOP4SERVICES.APPLICATIONID INCREMENT BY 1 
    START WITH 1 MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE 
    CACHE 10000 ORDER;   

CREATE TABLE MSPETSHOP4SERVICES.APPLICATIONS (
	ApplicationName varchar2(256 byte) NOT NULL, 
    ApplicationId number(10) NOT NULL, 
    Description varchar2(256 byte), 
    CONSTRAINT PK_APPLICATIONS PRIMARY KEY(ApplicationId),
    CONSTRAINT UC_APPLICATIONS1 UNIQUE(ApplicationName));

CREATE TABLE MSPETSHOP4SERVICES.USERS (
	UserId number NOT NULL, 
    ApplicationId number NOT NULL,
    UserName varchar2(256 byte) NOT NULL, 
    MobileAlias varchar2(16 byte),
    IsAnonymous char(1 byte) NOT NULL, 
    LastActivityDate date NOT NULL,
    CONSTRAINT PK_USERS PRIMARY KEY(UserId),
    CONSTRAINT FK_USERS_APPLICATIONS FOREIGN KEY(ApplicationId) 
		REFERENCES MSPETSHOP4SERVICES.APPLICATIONS(ApplicationId) 
		ON DELETE CASCADE);

CREATE TABLE MSPETSHOP4SERVICES.MEMBERSHIP (
	UserId number NOT NULL, 
    Password varchar2(128 byte) NOT NULL, 
    PasswordFormat number NOT NULL,
    MobilePin varchar2(16 byte),
    Email varchar2(128 byte), 
    PasswordQuestion varchar2(256 byte),
    PasswordAnswer varchar2(128 byte),   
    IsApproved char(1 byte) NOT NULL,
    CreatedDate date NOT NULL,
    LastLoginDate date NOT NULL,		
    LastPasswordChangedDate date NOT NULL,
    "Comment" varchar2(4000 byte),
    PasswordSalt varchar2(128 byte),
    CONSTRAINT PK_MEMBERSHIP PRIMARY KEY(UserId),
    CONSTRAINT FK_MEMBERSHIP_USERS FOREIGN KEY(UserId) 
		REFERENCES MSPETSHOP4SERVICES.USERS(UserId) 
		ON DELETE CASCADE);   

CREATE INDEX MSPETSHOP4SERVICES.IX_USERNAME 
    ON MSPETSHOP4SERVICES.USERS  (UserName) 
    TABLESPACE USERS;